package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.PersonType;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class PersonTypeDAO {
	
	/**
	 * Method that returns all Employers Person Types
	 * @return List<PersonType>
	 * @throws SQLException
	 */
	public List<PersonType> getCostumerTypes() throws SQLException {
		String query = "SELECT * FROM person_type WHERE description LIKE '%Costumer%'";
		List<PersonType> list = new LinkedList<PersonType>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			PersonType personType = new PersonType();
			personType.setId(result.getInt("id"));
			if (result.getString("description") != null)
				personType.setDescription(result.getString("description"));
			list.add(personType);
		}
		return list;
	}
	
	/**
	 * Method that returns all Costumer Person Types
	 * @return List<PersonType>
	 * @throws SQLException
	 */
	public List<PersonType> getEmployerTypes() throws SQLException {
		String query = "SELECT * FROM person_type WHERE description LIKE '%Employer%'";
		List<PersonType> list = new LinkedList<PersonType>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			PersonType personType = new PersonType();
			personType.setId(result.getInt("id"));
			if (result.getString("description") != null)
				personType.setDescription(result.getString("description"));
			list.add(personType);
		}
		return list;
	}
	
	/**
	 * Method that returns a specific Person Type for your id
	 * @param id Person Type Identifier
	 * @return PersonType
	 * @throws SQLException
	 */
	public PersonType getPersonType(int id) throws SQLException {
		String query = "SELECT * FROM person_type WHERE (id = " + id + " )";
		PersonType personType;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			personType = new PersonType();
			personType.setId(result.getInt("id"));
			if (result.getString("description") != null)
				personType.setDescription(result.getString("description"));
			ConnectionDB.getInstance().close();
			return personType;
		}
		return null;
	}
	
	/**
	 * Method that returns a specific Person Type for your description
	 * @param description Person Type Description
	 * @return PersonType
	 * @throws SQLException
	 */
	public PersonType getPersonType(String description) throws SQLException {
		String query = "SELECT * FROM person_type WHERE (description = '" + description + "' )";
		PersonType personType;
		ConnectionDB.getInstance().getConnection();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			personType = new PersonType();
			personType.setId(result.getInt("id"));
			if (result.getString("description") != null)
				personType.setDescription(result.getString("description"));
			ConnectionDB.getInstance().close();
			return personType;
		}
		return null;
	}
	
	/**
	 * Method that inserts a personType
	 * @param personType The PersonType to be inserted
	 * @return The ID of inserted PersonType
	 * @throws SQLException
	 */
	public int insert(PersonType personType) throws SQLException{
		String query = "INSERT INTO person_type (description) VALUES('" + personType.getDescription() + "')";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM person_type";
		int personTypeID = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return personTypeID;
	}
	
	/**
	 * Method that delete a PersonType
	 * @param personType The personType to be deleted
	 * @return if delete return true, else returns false
	 * @throws SQLException
	 */
	public boolean delete(PersonType personType){
		try{
			String query = "DELETE FROM person_type WHERE id = " + personType.getId();
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException SQLe) {
			SQLe.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that update a PersonType
	 * @param personType The personType to be updated
	 * @return If updtate returns true, else returns false
	 */
	public boolean update(PersonType personType){
		String query = "UPDATE parson_type SET ";
		query += "description = ' " + personType.getDescription() + " '";
		query += "WHERE id = "+personType.getId();
		try{
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException SQLe) {
			SQLe.printStackTrace();
			return false;
		}
	}

}
